SET sql_mode = 'ANSI_QUOTES';
DROP DATABASE IF EXISTS "uhdpbs_db";
CREATE DATABASE "uhdpbs_db";
USE "uhdpbs_db";

DROP TABLE IF EXISTS "patient";
CREATE TABLE "patient" (
	"id" SERIAL,
	"first_name" VARCHAR(32) NOT NULL,
	"last_name" VARCHAR(32) NOT NULL,
	"address" VARCHAR(128) NOT NULL,
	"city" VARCHAR(64) NOT NULL,
	"state" VARCHAR(2) NOT NULL,
	"zip" VARCHAR(5) NOT NULL,
	"phone" VARCHAR(15) NOT NULL,
	"gender" VARCHAR(1) NOT NULL,
	"dob" date NOT NULL,

	insurance_carrier VARCHAR(64) NOT NULL,
	insurance_group VARCHAR(64) NOT NULL,
	insurance_policy VARCHAR(64) NOT NULL
);

DROP TABLE IF EXISTS "practitioner";
CREATE TABLE "practitioner" (
	"id" SERIAL,
	"first_name" VARCHAR(32) NOT NULL,
	"last_name" VARCHAR(32) NOT NULL,
	"address" VARCHAR(128) NOT NULL,
	"city" VARCHAR(64) NOT NULL,
	"state" VARCHAR(2) NOT NULL,
	"zip" VARCHAR(5) NOT NULL,
	"phone" VARCHAR(15) NOT NULL,
	"gender" VARCHAR(1) NOT NULL,
	"dob" date,

	npi VARCHAR(64) NOT NULL,
	license_date date NOT NULL
);

DROP TABLE IF EXISTS "appointment";
CREATE TABLE "appointment" (
	"id" SERIAL,
	"date" DATE NOT NULL,
	"start" TIME NOT NULL,
	"stop" TIME NOT NULL,
	"patient_id"  BIGINT UNSIGNED NOT NULL,
	"practitioner_id"  BIGINT UNSIGNED NOT NULL
);

ALTER TABLE "appointment" ADD FOREIGN KEY "appointment_to_patient"("patient_id")
	REFERENCES "patient"("id")
	ON UPDATE RESTRICT
	ON DELETE RESTRICT;

ALTER TABLE "appointment" ADD FOREIGN KEY "appointment_to_practitioner"("practitioner_id")
	REFERENCES "practitioner"("id")
	ON UPDATE RESTRICT
	ON DELETE RESTRICT;

DROP TABLE IF EXISTS "visit";
CREATE TABLE "visit" (
	"id" SERIAL,
	"patient_id" bigint unsigned not null,
	"practitioner_id" bigint unsigned not null,
	"symptoms" varchar(255) not null,
	"notes" varchar(255) not null,
	"date" date not null,
	"is_printed" bool not null default 0,
	"cpt_codes" varchar(255) not null,
	"idc_codes" varchar(255),
	"price" float not null
);

ALTER TABLE "visit" ADD FOREIGN KEY "visit_to_patient"("patient_id")
	REFERENCES "patient"("id")
	ON UPDATE RESTRICT
	ON DELETE RESTRICT;

ALTER TABLE "visit" ADD FOREIGN KEY "visit_to_practitioner"("practitioner_id")
	REFERENCES "practitioner"("id")
	ON UPDATE RESTRICT
	ON DELETE RESTRICT;


DROP TABLE IF EXISTS "payment";
CREATE TABLE "payment" (
	"id" SERIAL,
	"patient_id" bigint unsigned not null,
	"date" date not null,
	"is_printed" bool not null default 0,
	"amount" float not null,
	"reference" varchar(64) not null
);

ALTER TABLE "payment" ADD FOREIGN KEY "payment_to_patient"("patient_id")
	REFERENCES "patient"("id")
	ON UPDATE RESTRICT
	ON DELETE RESTRICT;

